package vn.hocweb.dao.dao_implement;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import vn.hocweb.dao.dao_interface.IMaNguonDAO;
import vn.hocweb.entity.MaNguon;

public class MaNguonDAO extends GenericDAO implements IMaNguonDAO{

	@Override
	public MaNguon findByID(Long id) {
		Connection connection = getConnection();
		MaNguon result = null;
		String sql = "select * from manguon where manguon = ?";
		try {
			PreparedStatement pre = connection.prepareStatement(sql);
			pre.setLong(1, id);
			ResultSet effected = pre.executeQuery();
			if(effected.next()){
				long maNguon = effected.getLong("manguon");
				
				String ten = effected.getString("ten");
				String moTa = effected.getString("mota");
				String hinhAnh = effected.getString("hinhanh");
				String dinhDang = effected.getString("dinhdang");
				Date ngayCapNhat = effected.getDate("ngaycapnhat");
				int luotTaiVe = effected.getInt("luottaive");
				int thuTu = effected.getInt("thutu");
				boolean noiBat = effected.getBoolean("noibat");
				boolean hienThi = effected.getBoolean("hienthi");
				
				long maungdung = effected.getLong("maungdung");
				String nguoiCapNhat = effected.getString("nguoicapnhat");
				long maNguon_LienQuan = effected.getLong("manguon_lienquan");
				
				result = new MaNguon(maNguon, maungdung, nguoiCapNhat, maNguon_LienQuan, ten,
						moTa, hinhAnh, dinhDang, ngayCapNhat, luotTaiVe,
						thuTu, noiBat, hienThi);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		freeConnection(connection);
		return result;
	}

	public List<MaNguon> find_danhSach_MaNguonLienQuan(long maNguon_LienQuan, int firstItem, int batchSize) {
		Connection connection = getConnection();
		List<MaNguon> result = new ArrayList<MaNguon>();
		String sql = "select * from manguon where manguon_lienquan = ? and manguon <> ? limit ?, ?";
		try {
			PreparedStatement pre = connection.prepareStatement(sql);
			pre.setLong(1, maNguon_LienQuan);
			pre.setLong(2, maNguon_LienQuan);
			pre.setInt(3, firstItem);
			pre.setInt(4, batchSize);
			ResultSet effected = pre.executeQuery();
			while (effected.next()){
				long maNguon = effected.getLong("manguon");
				
				String ten = effected.getString("ten");
				String moTa = effected.getString("mota");
				String hinhAnh = effected.getString("hinhanh");
				String dinhDang = effected.getString("dinhdang");
				Date ngayCapNhat = effected.getDate("ngaycapnhat");
				int luotTaiVe = effected.getInt("luottaive");
				int thuTu = effected.getInt("thutu");
				boolean noiBat = effected.getBoolean("noibat");
				boolean hienThi = effected.getBoolean("hienthi");
				
				long maUngDung = effected.getLong("maungdung");
				String nguoiCapNhat = effected.getString("nguoicapnhat");
				long maNguon_lq = effected.getLong("manguon_lienquan");
				
				MaNguon row = new MaNguon(maNguon, maUngDung, nguoiCapNhat, maNguon_lq, ten,
						moTa, hinhAnh, dinhDang, ngayCapNhat, luotTaiVe,
						thuTu, noiBat, hienThi);
				result.add(row);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		freeConnection(connection);
		return result;
	}

	@Override
	public List<MaNguon> findAll(int firstItem, int batchSize) {
		Connection connection = getConnection();
		List<MaNguon> result = new ArrayList<MaNguon>();
		String sql = "select * from manguon limit ?, ?";
		try {
			PreparedStatement pre = connection.prepareStatement(sql);
			pre.setLong(1, firstItem);
			pre.setLong(2, batchSize);
			ResultSet effected = pre.executeQuery();
			while (effected.next()){
				long maNguon = effected.getLong("manguon");
				
				String ten = effected.getString("ten");
				String moTa = effected.getString("mota");
				String hinhAnh = effected.getString("hinhanh");
				String dinhDang = effected.getString("dinhdang");
				Date ngayCapNhat = effected.getDate("ngaycapnhat");
				int luotTaiVe = effected.getInt("luottaive");
				int thuTu = effected.getInt("thutu");
				boolean noiBat = effected.getBoolean("noibat");
				boolean hienThi = effected.getBoolean("hienthi");
				
				long maUngDung = effected.getLong("maungdung");
				String nguoiCapNhat = effected.getString("nguoicapnhat");
				long maNguon_LienQuan = effected.getLong("manguon_lienquan");
				
				MaNguon row = new MaNguon(maNguon, maUngDung, nguoiCapNhat, maNguon_LienQuan, ten,
						moTa, hinhAnh, dinhDang, ngayCapNhat, luotTaiVe,
						thuTu, noiBat, hienThi);
				result.add(row);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		freeConnection(connection);
		return result;
	}

	@Override
	public int size() {
		Connection connection = getConnection();
		int result = 0;
		String sql = "select count(*) from manguon";
		try {
			PreparedStatement pre = connection.prepareStatement(sql);
			ResultSet effected = pre.executeQuery();
			if(effected.next()){
				result = effected.getInt(0);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		freeConnection(connection);
		return result;
	}

	@Override
	public boolean save(MaNguon entity) {
		Connection connection = getConnection();
		boolean result = false;
		String sql = "insert into manguon (ten, mota, hinhanh, dinhdang, ngaycapnhat, " +
				"luottaive, thutu, noibat, hienthi, maungdung, nguoicapnhat, manguon_lienquan) " +
				"values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		try {
			PreparedStatement pre = connection.prepareStatement(sql);
			pre.setString(1, entity.getTen());
			pre.setString(2, entity.getMoTa());
			pre.setString(3, entity.getHinhAnh());
			pre.setString(4, entity.getDinhDang());
			pre.setDate(5, entity.getNgayCapNhat());
			pre.setInt(6, entity.getLuotTaiVe());
			pre.setInt(7, entity.getThuTu());
			pre.setBoolean(8, entity.isNoiBat());
			pre.setBoolean(9, entity.isHienThi());
			pre.setLong(10, entity.getUngDung().getMaBaiHoc());
			pre.setString(11, entity.getNguoiCapNhat().getTenDangNhap());
			pre.setLong(12, entity.getMaNguon_LienQuan().getMaNguon());
			
			int effected = pre.executeUpdate();
			if(effected > 0) result = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		freeConnection(connection);
		return result;
	}

	@Override
	public boolean delete(MaNguon entity) {
		Connection connection = getConnection();
		boolean result = false;
		String sql = "delete from manguon where manguon = ?";
		try {
			PreparedStatement pre = connection.prepareStatement(sql);
			pre.setLong(1, entity.getMaNguon());
			
			int effected = pre.executeUpdate();
			if(effected > 0) result = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		freeConnection(connection);
		return result;
	}

	@Override
	public boolean remove(MaNguon entity) {
		Connection connection = getConnection();
		boolean result = false;
		String sql = "update manguon set hienthi = ? where manguon = ?";
		try {
			PreparedStatement pre = connection.prepareStatement(sql);
			pre.setBoolean(1, false);
			pre.setLong(2, entity.getMaNguon());
			
			int effected = pre.executeUpdate();
			if(effected > 0) result = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		freeConnection(connection);
		return result;
	}

	@Override
	public boolean restore(MaNguon entity) {
		Connection connection = getConnection();
		boolean result = false;
		String sql = "update manguon set hienthi = ? where manguon = ?";
		try {
			PreparedStatement pre = connection.prepareStatement(sql);
			pre.setBoolean(1, true);
			pre.setLong(2, entity.getMaNguon());
			
			int effected = pre.executeUpdate();
			if(effected > 0) result = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		freeConnection(connection);
		return result;
	}

	@Override
	public List<MaNguon> findAllByMaUngDung(long maUngDung, int fistItem,
			int batchSize) {
		Connection connection = getConnection();
		List<MaNguon> result = new ArrayList<MaNguon>();
		String sql = "select * from manguon where maungdung = ? limit ?, ?";
		try {
			PreparedStatement pre = connection.prepareStatement(sql);
			pre.setLong(1, maUngDung);
			pre.setLong(2, fistItem);
			pre.setLong(3, batchSize);
			ResultSet effected = pre.executeQuery();
			while (effected.next()){
				long maNguon = effected.getLong("manguon");
				
				String ten = effected.getString("ten");
				String moTa = effected.getString("mota");
				String hinhAnh = effected.getString("hinhanh");
				String dinhDang = effected.getString("dinhdang");
				Date ngayCapNhat = effected.getDate("ngaycapnhat");
				int luotTaiVe = effected.getInt("luottaive");
				int thuTu = effected.getInt("thutu");
				boolean noiBat = effected.getBoolean("noibat");
				boolean hienThi = effected.getBoolean("hienthi");
				
				long maUD = effected.getLong("maungdung");
				String nguoiCapNhat = effected.getString("nguoicapnhat");
				long maNguon_LienQuan = effected.getLong("manguon_lienquan");
				
				MaNguon row = new MaNguon(maNguon, maUD, nguoiCapNhat, maNguon_LienQuan, ten,
						moTa, hinhAnh, dinhDang, ngayCapNhat, luotTaiVe,
						thuTu, noiBat, hienThi);
				result.add(row);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		freeConnection(connection);
		return result;
	}

}
